﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitor_ServerRoleMembers]
#-- Purpose:		Maintains the list of server (and database) role members
#--	Last Update:	02/10/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitor_ServerRoleMembers]
AS

SET NOCOUNT ON;

--- Declare Local Variables
DECLARE @sSQL varchar(MAX)
DECLARE @rights TABLE
(
	[srm_role_name] VARCHAR(128) NOT NULL,
	[srm_member_name] VARCHAR(512) NOT NULL,
	[srm_member_type] CHAR(1) NOT NULL,
	[srm_databasename] VARCHAR(256) NULL,
	[srm_member_disabled] BIT NOT NULL
)

--- Initialize Local Variables
SELECT	@sSQL = 'USE [?];
SELECT		r.[name] [srm_role_name],
			m.[name] [srm_member_name],
			m.[type] [srm_member_type],
			DB_NAME() [srm_databasename],
			(CASE	WHEN sp.[is_disabled] = 1 THEN 1
					WHEN p.[grantee_principal_id] IS NULL THEN 0
					ELSE 1
					END) [srm_member_disabled]
FROM		[sys].[database_principals] r
JOIN		[sys].[database_role_members] rm ON r.[principal_id] = rm.[role_principal_id]
JOIN		[sys].[database_principals] m ON m.[principal_id] = rm.[member_principal_id]
LEFT JOIN	[sys].[database_permissions] p ON m.[principal_id] = p.[grantee_principal_id]
			AND p.[type] = ''CO''
			AND p.[state] = ''D''
LEFT JOIN	[sys].[server_principals] sp ON m.[sid] = sp.[sid]
			OR m.[name] = sp.[name] COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE		r.[type] = ''R''
			AND NOT (
				r.[name] = ''db_owner''
				AND m.[name] = ''dbo''
			)

UNION

SELECT		perm.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS,
			pers.[name] COLLATE SQL_Latin1_General_CP1_CI_AS,
			pers.[type] COLLATE SQL_Latin1_General_CP1_CI_AS,
			DB_NAME() COLLATE SQL_Latin1_General_CP1_CI_AS,
			0
FROM		sys.database_permissions perm
JOIN		sys.database_principals pers ON perm.[grantee_principal_id] = pers.[principal_id]
WHERE		pers.[name] <> ''public''
			AND pers.[name] NOT LIKE ''##%''
			AND perm.[permission_name] <> ''CONNECT''
'

--- Gather Current Database Level Role Memberships
INSERT INTO @rights EXEC sp_MSforeachdb @sSQL

--- Gather Current Server Level Role Memberships
INSERT INTO @rights
	SELECT		r.[name] [srm_role_name],
				m.[name] [srm_member_name],
				m.[type] [srm_member_type],
				CAST(NULL as int) [srm_databaseid],
				m.[is_disabled] [srm_member_disabled]
	FROM		[sys].[server_principals] r
	JOIN		[sys].[server_role_members] rm ON r.[principal_id] = rm.[role_principal_id]
	JOIN		[sys].[server_principals] m ON m.[principal_id] = rm.[member_principal_id]
	WHERE		r.[type] = 'R'

	UNION

	SELECT		perm.[state_desc] + SPACE( 1 ) + perm.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS,
				pers.[name] COLLATE SQL_Latin1_General_CP1_CI_AS,
				pers.[type] COLLATE SQL_Latin1_General_CP1_CI_AS,
				NULL,
				0
	FROM		[sys].[server_permissions] perm
	JOIN		[sys].[server_principals] pers ON perm.[grantee_principal_id] = pers.[principal_id]
	WHERE		pers.[name] <> 'public'
				AND pers.[name] NOT LIKE '##%'
				AND perm.[permission_name] <> 'CONNECT SQL'


--- End "old" Role Members
UPDATE		Target
SET			[srm_end_date] = GETDATE()
FROM		@rights Source
RIGHT JOIN	[dbo].[tblMonitor_ServerRoleMembers] Target ON Target.[srm_role_name] = Source.[srm_role_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND Target.[srm_member_name] = Source.[srm_member_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND Target.[srm_member_type] = Source.[srm_member_type] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND Target.[srm_member_disabled] = Source.[srm_member_disabled]
			AND Target.[srm_end_date] IS NULL
			AND ISNULL(Target.[srm_databasename], '--NA--NA--') = ISNULL(Source.[srm_databasename], '--NA--NA--')
WHERE		Source.[srm_role_name] IS NULL

--- Insert Missing Role Members
INSERT INTO [dbo].[tblMonitor_ServerRoleMembers] (
	[srm_role_name],
	[srm_member_name],
	[srm_member_type],
	[srm_databasename],
	[srm_member_disabled]
)
	SELECT		Source.[srm_role_name],
				Source.[srm_member_name],
				Source.[srm_member_type],
				Source.[srm_databasename],
				Source.[srm_member_disabled]
	FROM		@rights Source
	LEFT JOIN	[dbo].[tblMonitor_ServerRoleMembers] Target ON Target.[srm_role_name] = Source.[srm_role_name] COLLATE SQL_Latin1_General_CP1_CI_AS
				AND Target.[srm_member_name] = Source.[srm_member_name] COLLATE SQL_Latin1_General_CP1_CI_AS
				AND Target.[srm_member_type] = Source.[srm_member_type] COLLATE SQL_Latin1_General_CP1_CI_AS
				AND Target.[srm_end_date] IS NULL
				AND ISNULL(Target.[srm_databasename], '--NA--NA--') = ISNULL(Source.[srm_databasename], '--NA--NA--')
	WHERE		Target.[srm_role_name] IS NULL;

SET NOCOUNT OFF;
